﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;
using System.Configuration;
using System.Data.Common;
using System.Reflection;
using System.ComponentModel;


namespace OMDRun
{


    /*
    * 描述:Dapper数据库操作列
    * 
    * @author 60058086【宋佳伟】
    * @version 1.0.0
    * @since 2025-04-23
    */

    public class DapperBase
    {

       /// <summary>
       /// 日志接口
       /// </summary>
        private log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        /// <summary>
        /// 构造函数
        /// </summary>
        private DapperBase() { }
        /// <summary>
        /// 项目库字典
        /// </summary>
        private  volatile  static  Dictionary<string, DapperBase> pairs = new Dictionary<string, DapperBase>();
        /// <summary>
        /// 对象锁
        /// </summary>
        public static object objLock = new object();
        /// <summary>
        /// 享元模式
        /// </summary>
        /// <param name="dbConnectionStr"></param>
        /// <returns></returns>
        public static DapperBase GetInstance(string dbConnectionStr)
        {
            lock (objLock)
            {
                if (!pairs.ContainsKey(dbConnectionStr))
                {
                    var instance = new DapperBase();
                    pairs.Add(dbConnectionStr, instance);
                    instance._dbConnectionStr = dbConnectionStr;
                }
                return pairs[dbConnectionStr];
            }
        }


        /// <summary>
        /// 中心库
        /// </summary>
        /// <returns></returns>
        //public static DapperBase GetInstance()
        //{
        //    string connProduct = AppSettings.GetItem("CentralDatabase").ToString();
        //    return GetInstance(connProduct);
        //}


        //数据库连接字符串
        public string _dbConnectionStr = string.Empty;



        /// <summary>
        /// 执行增删改操作(包括批量操作)
        /// </summary>
        /// <param name="sql">sql语句(有参数参数化)</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public bool Execute<T>(string sql, T t)
        {

            bool isSuccess = false;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        int result = DbConnection.Execute(sql, t);
                        isSuccess = true;
                    }
                    catch (Exception ex)
                    {

                        isSuccess = false; 
                        logger.Error(ex.Message);
                        throw ex;
                    }
                }
            }
            return isSuccess;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="tlist">列表</param>
        /// <returns></returns>
        public bool Execute<T>(string sql, List<T> tlist)
        {
            bool isSuccess = false;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        
                        int result = DbConnection.Execute(sql, tlist);
                        isSuccess = result > 0 ? true : false;
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                        isSuccess = false;
                    }
                }
            }
            return isSuccess;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql)
        {
            object obj = null;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        obj = DbConnection.ExecuteScalar(sql);

                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                    }
                }
            }
            return obj;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="msg">返回信息</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql ,out string msg)
        {
            object obj = null;
            msg = null;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        obj = DbConnection.ExecuteScalar(sql);

                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                        msg = ex.Message;
                    }
                }
            }
            return obj;
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="msg">返回信息</param>
        /// <returns></returns>
        public object TransactionExecuteScalar(string sql, out string msg)
        {
            object obj = null;
            msg = null;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    DbConnection.Open();
                    IDbTransaction transaction = DbConnection.BeginTransaction();
                    try
                    {
                        obj = DbConnection.ExecuteScalar(sql,transaction: transaction);
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        logger.Error(ex.Message);
                        msg = ex.Message;
                    }
                    finally
                    {
                        DbConnection.Close();
                    }
                }
            }
            return obj;
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="t">对象</param>
        /// <returns></returns>
        public object ExecuteScalar<T>(string sql, T t)
        {
            object obj = null;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        obj = DbConnection.ExecuteScalar(sql,t);

                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                    }
                }
            }
            return obj;
        }


        /// <summary>
        /// 是否存在表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool IsExist(string tableName)
        {
            string sql = " select top 1 'X' from sysObjects where Id=OBJECT_ID(N'" + tableName + "') and xtype='U'";
            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {
                try
                {
                    var obj = DbConnection.ExecuteScalar(sql);
                    return obj != null;
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                }
            }
            return false;
        }

        /// <summary>
        /// 是否存在列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public bool IsExistColumn(string tableName,string columnName)
        {
            string sql = $"select top 1 'X' from syscolumns where id=object_id('{tableName}') and name='{columnName}'";
            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {
                try
                {
                    var obj = DbConnection.ExecuteScalar(sql);
                    return obj != null;
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                }
            }
            return false;
        }
        /// <summary>
        /// 是否存在存储过程
        /// </summary>
        /// <param name="procName">存储过程</param>
        /// <returns></returns>
        public bool IsExistProc(string procName)
        {
            string sql = $"select OBJECT_ID(N'{procName}','P')";
            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {
                try
                {
                    var obj = DbConnection.ExecuteScalar(sql);
                    return obj != null;
                }
                catch (Exception ex)
                {
                    logger.Error(ex.Message);
                }
            }
            return false;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public bool Execute(string sql ,int timeout=1200)
        {
            bool isSuccess = false;
            if (!String.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        int result = DbConnection.Execute(sql,commandTimeout: timeout);
                        isSuccess = true;
                    }
                    catch (Exception ex)
                    {
                        LogHelper.LogError(ex.Message);
                        logger.Error(ex.Message);
                        isSuccess = false;
                    }
                   
                }
            }
            return isSuccess;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="t">对象</param>
        /// <param name="ignore">忽略字段</param>
        /// <returns></returns>
        public bool Insert<T>(T t, params string[] ignore)
        {
            return this.Execute(this.InsertString(t, ignore), t);
        }
        /// <summary>
        /// 插入批次
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="list">列表</param>
        /// <param name="ignore">忽略字段</param>
        /// <returns></returns>
        public bool InsertPatch<T>(List<T> list, params string [] ignore  ) where T : new()
        {

            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {
                DbConnection.Open();
                //开始事务
                IDbTransaction transaction = DbConnection.BeginTransaction();
                try
                {

                    DbConnection.Execute(InsertString(new T(), ignore), list, transaction, null, null);
                    //提交事务
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    logger.Error(ex.Message);
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 更新表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="t">对象</param>
        /// <param name="ss">忽略字段</param>
        /// <returns></returns>
        public bool Update<T>(T t, params string[] ss)
        {
            return this.Execute(this.UpdateString(t, ss), t);
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="list">列表</param>
        /// <param name="ss">忽略字段</param>
        /// <returns></returns>
        public bool UpdatePatch<T>(List<T> list, params string[] ss) where T : new()
        {

            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {

                //DbConnection.Open();
                //开始事务
                IDbTransaction transaction = DbConnection.BeginTransaction();
                try
                {
                    DbConnection.Execute(UpdateString(new T(), ss), list, transaction, null, null);
                    //提交事务
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    logger.Error(ex.Message);
                    return false;
                }
            }
            return true;
        }



        /// <summary>
        /// 执行存储过程操作
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        /// <param name="param">参数化值</param>
        /// <returns>返回存储过程是否执行成功</returns>
        public bool ExecuteProc(string storedName, DynamicParameters param = null)
        {
            bool isSuccess = false;
            if (!String.IsNullOrEmpty(storedName))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        int result = DbConnection.Execute(storedName, param, commandType: CommandType.StoredProcedure);
                        isSuccess = true;
                    }
                    catch
                    {
                        isSuccess = false;
                    }
                }
            }
            return isSuccess;
        }

        /// <summary>
        /// 执行存储过程操作
        /// </summary>
        /// <param name="storedName">存储过程名称</param>
        /// <param name="param">存储过程参数</param>
        /// <returns>返回存储过程要返回的值</returns>
        public DynamicParameters ExecuteStored(string storedName, DynamicParameters param)
        {
            if (!String.IsNullOrEmpty(storedName))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        DbConnection.Execute(storedName, param, commandType: CommandType.StoredProcedure);
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                    }
                }
            }
            return param;
        }


        /// <summary>
        /// 查询操作
        /// </summary>
        /// <typeparam name="T">返回集合的类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string sql, object param=null)
        {
            IEnumerable<T> _list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        if (param == null)
                        {
                            _list = DbConnection.Query<T>(sql);
                        }
                        else
                        {
                            _list = DbConnection.Query<T>(sql, param);
                        }
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                    }

                }
            }
            return _list;
        }

        /// <summary>
        /// 查询操作
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public IEnumerable<T> Query<T>(string sql)
        {
            IEnumerable<T> _list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {

                        _list = DbConnection.Query<T>(sql);

                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                    }

                }
            }
            return _list;
        }

        /// <summary>
        /// 执行存储过程查询操作
        /// </summary>
        /// <typeparam name="T">返回集合的类型</typeparam>
        /// <param name="storedName">存储过程</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public IEnumerable<T> QueryStored<T>(string storedName, object param)
        {
            IEnumerable<T> _list = default(IEnumerable<T>);
            if (!string.IsNullOrEmpty(storedName))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        _list = DbConnection.Query<T>(storedName, param, commandType: CommandType.StoredProcedure);
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                    }
                }
            }
            return _list;
        }

        /// <summary>
        /// 查询操作返回默认第一条数据(如返回null则创建默认类型)
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public T FirstOrDefault<T>(string sql, object param=null)
        {
            var model = default(T);
            if (!string.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        model = DbConnection.Query<T>(sql, param).FirstOrDefault();
                    }
                    catch (Exception ex)
                    {
                        logger.Error(ex.Message);
                        throw ex;
                    }
                }
            }
            //return model == null ? Activator.CreateInstance<T>() : model;
            return model;
        }



        /// <summary>
        /// 查询一组SQL语句并返回值
        /// </summary>
        /// <typeparam name="T1">第一条语句返回集合类型</typeparam>
        /// <typeparam name="T2">第二条语句返回集合类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public Tuple<IEnumerable<T1>, IEnumerable<T2>> Query<T1, T2>(string sql, object param)
        {
            IEnumerable<T1> _item1 = null; IEnumerable<T2> _item2 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        using (var multi = DbConnection.QueryMultiple(sql, param))
                        {
                            _item1 = multi.Read<T1>();
                            _item2 = multi.Read<T2>();
                        }
                    }
                    catch (Exception ex)
                    {

                        logger.Error(ex.Message);
                    }
                }
            }
            return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>>(_item1, _item2);
        }

        /// <summary>
        /// 查询一组SQL语句并返回值
        /// </summary>
        /// <typeparam name="T1">第一条语句返回集合类型</typeparam>
        /// <typeparam name="T2">第二条语句返回集合类型</typeparam>
        /// <typeparam name="T3">第三条语句返回集合类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数化值</param>
        /// <returns></returns>
        public Tuple<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>> Query<T1, T2, T3>(string sql, object param)
        {
            IEnumerable<T1> _item1 = null; IEnumerable<T2> _item2 = null; IEnumerable<T3> _item3 = null;
            if (!string.IsNullOrEmpty(sql))
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    try
                    {
                        using (var multi = DbConnection.QueryMultiple(sql, param))
                        {
                            _item1 = multi.Read<T1>();
                            _item2 = multi.Read<T2>();
                            _item3 = multi.Read<T3>();
                        }
                    }
                    catch (Exception ex)
                    {

                        logger.Error(ex.Message);
                    }
                }
            }
            return Tuple.Create<IEnumerable<T1>, IEnumerable<T2>, IEnumerable<T3>>(_item1, _item2, _item3);
        }



        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="sqllist">sql语句集合</param>
        /// <returns></returns>
        public bool TransactionExcute(List<string> sqllist)
        {

            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {
                DbConnection.Open();
                //开始事务
                IDbTransaction transaction = DbConnection.BeginTransaction();
                try
                {
                    foreach (string sql in sqllist)
                    {
                        DbConnection.Execute(sql, null, transaction, null, null);
                    }

                    //提交事务
                    transaction.Commit();

                    return true;
                }
                catch (Exception ex)
                {
                    //出现异常，事务Rollback
                    transaction.Rollback();
                    logger.Error(ex.Message);
                    return false;
                }
            }

        }

        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="dic">sql集合</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public bool TransactionExcute(Dictionary<string, object> dic)
        {
            try
            {
                using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
                {
                    DbConnection.Open();
                    IDbTransaction transaction = DbConnection.BeginTransaction();
                    try
                    {
                        //开始事务


                        foreach (KeyValuePair<string, object> pair in dic)
                        {
                            DbConnection.Execute(pair.Key, pair.Value, transaction, null, null);
                        }

                        //提交事务
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        //出现异常，事务Rollback
                        transaction.Rollback();
                        logger.Error(ex.Message);
                        return false;

                    }
                    finally
                    {
                        DbConnection.Close();
                    }
                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }





        /// <summary>
        /// 插入语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="t">对象</param>
        /// <param name="ignore">忽略字段</param>
        /// <returns></returns>
        public string InsertString<T>(T t, params string[] ignore)
        {
            string tableName = t.GetType().Name;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
            {
                dic.Add(info.Name, info.GetValue(t, null));
            }

            StringBuilder sbCol = new System.Text.StringBuilder();
            StringBuilder sbVal = new System.Text.StringBuilder();

            foreach (KeyValuePair<string, object> kv in dic)
            {


                //if (kv.Key == "Id") {

                //    PropertyInfo propertyInfo = typeof(T).GetProperties().FirstOrDefault(s=>s.Name=="Id");
                //    IdentityAttribute descAttr = Attribute.GetCustomAttribute(propertyInfo, typeof(IdentityAttribute)) as IdentityAttribute;
                //    if (descAttr != null)
                //        continue;
                //}

                if (sbCol.Length == 0)
                {
                    sbCol.Append("insert into ");
                    sbCol.Append(tableName);
                    sbCol.Append("(");
                }


                if (!ignore.Contains(kv.Key))
                {
                    sbCol.Append(kv.Key);
                    sbCol.Append(",");
                }


                if (sbVal.Length == 0)
                {
                    sbVal.Append(" values(");
                }

                if (!ignore.Contains(kv.Key))
                {
                    sbVal.Append("@");
                    sbVal.Append(kv.Key);
                    sbVal.Append(",");
                }

            }
            //sbCol.Append(") ");
            //sbVal.Append(") ");

            return sbCol.ToString().Trim(',') + ")" + sbVal.ToString().Trim(',') + ")";
        }

        /// <summary>
        /// 更新数据 
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="t">对象</param>
        /// <param name="ss">条件字段</param>
        /// <returns></returns>
        private string UpdateString<T>(T t, params string[] ss)
        {
            string tableName = t.GetType().Name;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
            {
                dic.Add(info.Name, info.GetValue(t, null));
            }
            StringBuilder sbCol = new System.Text.StringBuilder();
            foreach (KeyValuePair<string, object> kv in dic)
            {
                if (sbCol.Length == 0)
                {
                    sbCol.Append("Update ");
                    sbCol.Append(tableName);
                    sbCol.Append(" Set ");
                }
                if (!ss.Contains(kv.Key))
                {
                    sbCol.Append(kv.Key + "=@" + kv.Key);
                    sbCol.Append(",");
                }

            }

            StringBuilder sbWhere = new System.Text.StringBuilder();
            if (ss.Length == 0)
            {
                sbWhere.Append(" Where Id=@Id ");
            }
            else
            {
                sbWhere.Append(" Where  1=1 ");
                foreach (string str in ss)
                {
                    sbWhere.Append(" AND  " + str + "=@" + str);
                }
            }
            return sbCol.ToString().Trim(',') + " " + sbWhere.ToString();
        }



        /// <summary>
        /// 更新语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="t">对象</param>
        /// <param name="ignore">忽略字段</param>
        /// <param name="ss">条件字段</param>
        /// <returns></returns>
        public string UpdateString<T>(T t, List<string> ignore = null, params string[] ss)
        {
            string tableName = t.GetType().Name;
            Dictionary<string, object> dic = new Dictionary<string, object>();
            foreach (System.Reflection.PropertyInfo info in t.GetType().GetProperties())
            {
                dic.Add(info.Name, info.GetValue(t, null));
            }
            StringBuilder sbCol = new System.Text.StringBuilder();
            foreach (KeyValuePair<string, object> kv in dic)
            {
                if (sbCol.Length == 0)
                {
                    sbCol.Append("Update ");
                    sbCol.Append(tableName);
                    sbCol.Append(" Set ");
                }
                if (!ss.Contains(kv.Key) && !ignore.Contains(kv.Key))
                {
                    sbCol.Append(kv.Key + "=@" + kv.Key);
                    sbCol.Append(",");
                }

            }

            StringBuilder sbWhere = new System.Text.StringBuilder();
            if (ss.Length == 0)
            {
                sbWhere.Append(" Where Id=@Id ");
            }
            else
            {
                sbWhere.Append(" Where  1=1 ");
                foreach (string str in ss)
                {
                    sbWhere.Append(" AND  " + str + "=@" + str);
                }
            }
            return sbCol.ToString().Trim(',') + " " + sbWhere.ToString();
        }



        /// <summary>
        /// 批量复制插入数据
        /// </summary>
        /// <param name="dataTable">数据集</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool SqlBulkCopyInsertTable(DataTable dataTable, string tableName)
        {
            try
            {

                using (System.Data.SqlClient.SqlBulkCopy bulk = new System.Data.SqlClient.SqlBulkCopy(_dbConnectionStr, SqlBulkCopyOptions.KeepIdentity))
                {

                    bulk.DestinationTableName = tableName; //要导入的数据表名
                    bulk.BatchSize = dataTable.Rows.Count;    //每一批次中的行数

                    foreach (DataColumn dc in dataTable.Columns)
                    {
                        bulk.ColumnMappings.Add(dc.Caption, dc.Caption);    //设置数据源中的列和目标表中的列之间的映射关系
                    }
                    bulk.WriteToServer(dataTable);
                    if (bulk != null)
                    {
                        bulk.Close();
                    }
                }

                return true;
            }

            catch (Exception ex)
            {
                logger.Error(ex.Message);
                return false;
            }

        }


        /// <summary>
        /// 收缩数据库
        /// </summary>
        public void ShrinkDataBase()
        {
            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {
                string sql = "DBCC SHRINKDATABASE(" + DbConnection.Database + ")";
                DbConnection.Execute(sql);
            }

        }


        /// <summary>
        /// 生成插入更新语句
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t">实体</param>
        /// <param name="conditionCol">条件列</param>
        /// <param name="tableName">表明</param>
        /// <returns></returns>
        public string InsertUpdateString<T>(T t, string conditionCol, params string[] ignore)
        {
            string tableName = t.GetType().Name;

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(string.Format("if exists(select 1 from {1} where {0} = @{0})", conditionCol, tableName));
            stringBuilder.AppendLine(UpdateString(t, conditionCol));
            stringBuilder.AppendLine("else");
            stringBuilder.AppendLine(InsertString(t, ignore));
            return stringBuilder.ToString();
        }


        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// <param name="files">列</param>
        /// <param name="tableName">表</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public IEnumerable<T> GetPageList<T>(string cols, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total, out int pageCount)
        {
            using (IDbConnection DbConnection = new SqlConnection(_dbConnectionStr))
            {

                int skip = 1;
                if (pageIndex > 0)
                {
                    skip = (pageIndex - 1) * pageSize + 1;
                }
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
                sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", cols, tableName, where, orderby, skip, pageIndex * pageSize);
                using (var reader = DbConnection.QueryMultiple(sb.ToString()))
                {
                    total = reader.ReadFirst<int>();
                    pageCount = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;

                    return reader.Read<T>();
                }
            }
        }

        /// <summary>
        /// 获取服务器时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetServerTime()
        {
            var obj = ExecuteScalar("Select GETDATE()");
            return obj != null ? (DateTime)obj : DateTime.Now;
        }



        /// <summary>
        /// 获取对象的所有属性值，并将它们作为数组返回
        /// </summary>
        /// <param name="obj">要处理的对象</param>
        /// <returns>包含对象所有属性值的数组</returns>
        public static string GetPropertyNames( Type type)
        {
            // 使用反射获取对象的所有属性
            PropertyInfo[] properties = type.GetProperties();

            // 创建一个与属性数量相同大小的数组，用于存储属性值
            string[] names = new string[properties.Length];

            // 遍历所有属性，将属性值添加到数组中
            for (int i = 0; i < properties.Length; i++)
            {


                names[i] = properties[i].Name;
            }
            return  string.Join(",", names);
        }

    }



    /// <summary>
    /// DataTable与实体类互相转换
    /// </summary>
    /// <typeparam name="T">实体类</typeparam>
    public class ModelHandler<T> where T : new()
    {
        #region DataTable转换成实体类



        /// <summary>
        /// 复制属性
        /// </summary>
        /// <param name="source">源对象</param>
    
        public static T MapTo(object source)
        {
            var sourceType = source.GetType();
            T destination = new T();
            var destinationType = destination.GetType();

            var sourceProperties = sourceType.GetProperties();
            var destinationProperties = destinationType.GetProperties();

            foreach (var sourceProperty in sourceProperties)
            {
                var destinationProperty = destinationProperties.FirstOrDefault(p => p.Name == sourceProperty.Name && p.PropertyType == sourceProperty.PropertyType);
                if (destinationProperty != null)
                {
                    destinationProperty.SetValue(destination, sourceProperty.GetValue(source, null), null);
                }
            }
            return destination;
        }

        ///// <summary>
        ///// 填充对象列表：用DataSet的第一个表填充实体类
        ///// </summary>
        ///// <param name="ds">DataSet</param>
        ///// <returns></returns>
        //public List<T> FillModel(DataSet ds)
        //{
        //    if (ds == null || ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0)
        //    {
        //        return null;
        //    }
        //    else
        //    {
        //        return FillModel(ds.Tables[0]);
        //    }
        //}

        ///// <summary>  
        ///// 填充对象列表：用DataSet的第index个表填充实体类
        ///// </summary>  
        //public List<T> FillModel(DataSet ds, int index)
        //{
        //    if (ds == null || ds.Tables.Count <= index || ds.Tables[index].Rows.Count == 0)
        //    {
        //        return null;
        //    }
        //    else
        //    {
        //        return FillModel(ds.Tables[index]);
        //    }
        //}

        /// <summary>  
        /// 填充对象列表：用DataTable填充实体类
        /// </summary>  
        public static List<T> FillModel(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }
            List<T> modelList = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                //T model = (T)Activator.CreateInstance(typeof(T));  
                T model = new T();
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                    if (propertyInfo != null && dr[i] != DBNull.Value)
                        propertyInfo.SetValue(model, dr[i], null);
                }

                modelList.Add(model);
            }
            return modelList;
        }

        /// <summary>  
        /// 填充对象：用DataRow填充实体类
        /// </summary>  
        public T FillModel(DataRow dr)
        {
            if (dr == null)
            {
                return default(T);
            }

            //T model = (T)Activator.CreateInstance(typeof(T));  
            T model = new T();

            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                if (propertyInfo != null && dr[i] != DBNull.Value)
                    propertyInfo.SetValue(model, dr[i], null);
            }
            return model;
        }

        #endregion

        #region 实体类转换成DataTable

        /// <summary>
        /// 实体类转换成DataTable
        /// </summary>
        /// <param name="modelList">实体类列表</param>
        /// <returns></returns>
        public static DataTable FillDataTable(List<T> modelList)
        {
            if (modelList == null || modelList.Count == 0)
            {
                return null;
            }
            DataTable dt = CreateData(new T());

            foreach (T model in modelList)
            {
                DataRow dataRow = dt.NewRow();
                foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
                {
                    //DescriptionAttribute descAttr = Attribute.GetCustomAttribute(propertyInfo, typeof(DescriptionAttribute)) as DescriptionAttribute;
                    dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null);
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 根据实体类得到表结构
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public static DataTable CreateData(T model)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
            {

                dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType));

            }
            return dataTable;
        }

       

        #endregion
    }


}